package org.novelhibernate.core.transaction;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.novelhibernate.core.ArgumentWrapper;
import org.novelhibernate.core.NestedStatement;
import org.novelhibernate.core.ParameterMapper;
import org.novelhibernate.core.annotation.PojoForeignEntityDeclaration;
import org.novelhibernate.core.annotation.PojoNonMapDeclaration;
import org.novelhibernate.core.sqlregistry.AbstractSQLRegistry;
import org.novelhibernate.exception.InproperDivisionOfSQLException;
import org.novelhibernate.exception.NovelhibernateException;
import org.novelhibernate.util.SQLParser;

/**
 * Some rules that needs to be aware:
 * 
 * 1. any attributes that belong to external entity (including foreign key),
 * needs to be stated as alias parameters, alias and the other parameters that must 
 * be stated in advance, before the operation method called (for those FK stated in 'insert into (id, FK) values' and
 * 'update ... set FK=?' will be automatically detected and acquire on the target object)
 * 
 * 
 * 
 * 2. for update insertion and deletion, the main targets are the value object
 * or objects, no HQL supported, therefore foreign key needs to be stated
 * 
 * 3. for update and delete, statement likes id in (?,?,?) are not supported in
 * dynamic load from object, use this with the specific injection (alias or predefined parameter), beside,
 * usually when id in (?,?,?) is needed, in which case can be replaced by id=? with
 * multiple objects. foreign key can not be added alias, it needs to be stated
 * in alias parameter, also internal function like toDate(?), number = number + ? etc. 
 * are also ignored, manual specification needed. 
 * 
 * 4. Sequence of attribute must be match the table, as well as the attribute name, at least
 * the attribute name used in sql should match the attribute name in entity and it is case sensitive.
 * 
 * Be aware for delete sql, due to it is not allowed
 * to use alias in it, therefore any attributes in sql that has not been predefined (pre-assgined) would
 * be executed as acquire from the target object
 * 
 * However, for update and delete, target object is optional but recommend to do it in an OO way
 * 
 * SQL needs to stated in lower case
 * 
 * specific injection can be also used for those parameters that does not bind
 * to a specific object
 * 
 * alias needed on the table that needs to be manipulated, e.g message as m, and the m should not be put on a FK
 * and alias should be add for the criteria in where statement
 * 
 * Note that due to performance concern, there are no synchronization
 * implemented here, therefore it needs to be used in a thread safe manner (get
 * transaction as method level variable)
 * 
 * if alias parameter has been registered on registry, then it will not needs to
 * be stated again when actually use it
 * 
 * @author Tao Chen
 * 
 */
public class TransactionImpl extends TransactionSupport implements Transaction {

	private static final Logger log = Logger.getLogger(TransactionImpl.class);

	// this is not thread safe
	private Connection conn;
	// this is not thread safe
	private LinkedList<Object> resultList;
	// this is not thread safe
	private NestedStatement currentStatement;

	private List<NestedStatement> allStatements;
	// this is thread safe
	private SQLParser parser;

	private AbstractSQLRegistry registry = AbstractSQLRegistry.getInstance();

	/**
	 * 
	 * @param conn
	 * @param autoCommit
	 * @param transactionIsolation
	 * @param isRecordResult
	 * @throws SQLException
	 */
	public TransactionImpl(Connection conn, boolean autoCommit,
			int transactionIsolation, boolean isRecordResult)
			throws SQLException {

		this.conn = conn;
		conn.setTransactionIsolation(transactionIsolation);
		conn.setAutoCommit(autoCommit);

		if (isRecordResult)
			resultList = new LinkedList<Object>();

		parser = SQLParser.getInstance();

	}

	/**
	 * 
	 * @param conn
	 * @param isRecordResult
	 * @throws SQLException
	 */
	public TransactionImpl(Connection conn, boolean isRecordResult)
			throws SQLException {
		
		this.conn = conn;
		if (isRecordResult)
			resultList = new LinkedList<Object>();

		parser = SQLParser.getInstance();

	}

	@SuppressWarnings("unchecked")
	public Transaction save(String sql, Object target) throws SQLException,
			Throwable {

		log.debug("Save start " + sql);
		if (target == null)
			return this;
		// In case that user apply nested batch operation without the ended batch trigger (batch*)
		if (null != allStatements)
			throw new NovelhibernateException("Nested statemenet can not be end up with non-batch statement");

		PreparedStatement prep = null;
		try {

			prep = conn.prepareStatement(sql);
			// the main target always come first, then is the alternative
			// variables with the same sequence as
			// in pojo as well as the actual table
			int result = 0;

			Object list = registry.getRegisteredValue(sql
					.replaceAll("\\s*", ""));

			if (null != list) {

				List<ArgumentWrapper> wrappers = (List<ArgumentWrapper>) list;

				for (int i = 0; i < wrappers.size(); i++)
					wrappers.get(i).getPreparedStatementSetMethod().invoke(
							prep,
							i + 1,
							callNestedMethod(target, wrappers.get(i)
									.getMethodArray(), 0));

				result = prep.executeUpdate();

			} else
				result = executeInsertStatement(null, prep, target, true)
						.executeUpdate();

			if (null != resultList)
				resultList.add(result);

		} catch (SQLException e) {

			log.error("Save failed failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Save failed failed", e);
			rollback();
			throw e;

		} finally {

			prep.close();
			currentStatement = null;

		}

		log.debug("Save successfully");
		return this;

	}

	@SuppressWarnings("unchecked")
	public Transaction batchSave(String sql, Object[] targets,
			boolean isReversed) throws SQLException, Throwable {

		log.debug("Batch save start " + sql);

		if (targets == null || targets.length == 0)
			return this;

		NestedStatement state = null;

		if (currentStatement == null)
			state = new NestedStatement(sql, targets);
		else {
			state = currentStatement;
			state.setSql(sql);
			state.setTargets(targets);
		}

		state.setStatements(allStatements);
		state.setReversed(isReversed);
		state.setOperation(Transaction.SAVE);
		state.setRoot(true);

		try {

			processBatchOperation(state, 0);

			state.execute(resultList);

		} catch (SQLException e) {

			log.error("Save failed failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Save failed failed", e);
			rollback();
			throw e;

		} finally {

			state.release();
			allStatements = null;
			currentStatement = null;

		}

		log.debug("Save successfully");
		return this;

	}

	@SuppressWarnings("unchecked")
	public Transaction update(String sql, Object target)
			throws InproperDivisionOfSQLException, SQLException, Throwable {

		log.debug("Update start " + sql);
		
		if (null != allStatements)
			throw new NovelhibernateException("Nested statemenet can not be end up with non-batch statement");

		PreparedStatement prep = null;
		try {

			prep = conn.prepareStatement(sql);

			int result = 0;

			Object list = registry.getRegisteredValue(sql
					.replaceAll("\\s*", ""));
			
			// Execute those predefined parameters first, no matter if the sql has been
			// previously parsed, as some of the parameter may come from run-time thus could not
			// be parsed in advance

			if (null != currentStatement
					&& null != currentStatement.getExtraMapper())
				for (ParameterMapper mapper : currentStatement.getExtraMapper())
					mapper.getPreparedStatementSetMethod().invoke(prep, mapper.getIndex() + 1,
							mapper.getParameter());

			if (null != list) {

				List<ArgumentWrapper> wrappers = (List<ArgumentWrapper>) list;

				// Add those parameters extract from objects, (alias elements have been parsed)
				for (int i = 0; i < wrappers.size(); i++)
					wrappers.get(i).getPreparedStatementSetMethod().invoke(
							prep,
							wrappers.get(i).getIndex() + 1,
							callNestedMethod(target, wrappers.get(i)
									.getMethodArray(), 0));

				result = prep.executeUpdate();
            
			// Otherwise extract nomarl data from target as well as the predefined 
			// alias data, if any	
			} else
				result = executeUpdateAndDeleteStatement(
						null,
						prep,
						target,
						parser.parseUpdateSQL(sql, (target == null)? null : target.getClass()),
						(currentStatement == null) ? null : currentStatement
								.getAliasMapper(), true).executeUpdate();

			if (null != resultList)
				resultList.add(result);

		} catch (SQLException e) {

			log.error("Update failed failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Update failed failed", e);
			rollback();
			throw e;

		} finally {

			prep.close();
			allStatements = null;
			currentStatement = null;
		}

		log.debug("Update successfully");
		return this;

	}

	public Transaction batchUpdate(String sql, Object[] targets,
			boolean isReversed) throws InproperDivisionOfSQLException,
			SQLException, Throwable {

		if (targets == null || targets.length == 0)
			return this;

		log.debug("Batche update start " + sql);

		NestedStatement state = null;

		if (currentStatement == null)
			state = new NestedStatement(sql, targets);
		else {
			state = currentStatement;
			state.setSql(sql);
			state.setTargets(targets);
		}
		state.setStatements(allStatements);
		state.setOperation(Transaction.UPDATE);
		state.setReversed(isReversed);
		state.setRoot(true);

		try {

			processBatchOperation(state, 0);

			state.execute(resultList);
		} catch (SQLException e) {

			log.error("Update failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Update failed", e);
			rollback();
			throw e;

		} finally {

			state.release();
			allStatements = null;
			currentStatement = null;

		}

		log.debug("Update successfully");

		return this;

	}

	@SuppressWarnings("unchecked")
	public Transaction delete(String sql, Object target)
			throws InproperDivisionOfSQLException, SQLException, Throwable {

		log.debug("Delete start " + sql);


		if (null != allStatements)
			throw new NovelhibernateException("Nested statemenet can not be end up with non-batch statement");
		
		PreparedStatement prep = null;
		try {

			prep = conn.prepareStatement(sql);

			int result = 0;

			Object list = registry.getRegisteredValue(sql
					.replaceAll("\\s*", ""));

			if (null != list) {

				List<ArgumentWrapper> wrappers = (List<ArgumentWrapper>) list;
				if (null != currentStatement
						&& null != currentStatement.getExtraMapper())
					for (ParameterMapper mapper : currentStatement
							.getExtraMapper())
						mapper.getPreparedStatementSetMethod().invoke(prep, mapper.getIndex() + 1,
								mapper.getParameter());

				for (int i = 0; i < wrappers.size(); i++)
					wrappers.get(i).getPreparedStatementSetMethod().invoke(
							prep,
							wrappers.get(i).getIndex() + 1,
							callNestedMethod(target, wrappers.get(i)
									.getMethodArray(), 0));

				result = prep.executeUpdate();

			} else {

				List<Integer> restrict = new ArrayList<Integer>();
				if (null != currentStatement
						&& null != currentStatement.getExtraMapper())
					for (ParameterMapper mapper : currentStatement
							.getExtraMapper())
						restrict.add(mapper.getIndex());

				if (null != currentStatement
						&& null != currentStatement.getAliasMapper())
					for (ParameterMapper mapper : currentStatement
							.getAliasMapper())
						restrict.add(mapper.getIndex());

				result = executeUpdateAndDeleteStatement(
						null,
						prep,
						target,
						parser.parseDeleteSQL(sql, restrict),
						(currentStatement == null) ? null : currentStatement
								.getAliasMapper(), true).executeUpdate();

			}
			if (null != resultList)
				resultList.add(result);

		} catch (SQLException e) {

			log.error("Delete failed failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Delete failed failed", e);
			rollback();
			throw e;

		} finally {

			prep.close();
			allStatements = null;
			currentStatement = null;

		}

		log.debug("Delete successfully");
		return this;
	}

	public Transaction batchDelete(String sql, Object[] targets,
			boolean isReversed) throws InproperDivisionOfSQLException,
			SQLException, Throwable {

		if (targets == null || targets.length == 0)
			return this;

		log.debug("Batche delete start " + sql);

		NestedStatement state = null;

		if (currentStatement == null)
			state = new NestedStatement(sql, targets);
		else {
			state = currentStatement;
			state.setSql(sql);
			state.setTargets(targets);
		}

		state.setStatements(allStatements);
		state.setOperation(Transaction.DELETE);
		state.setReversed(isReversed);
		state.setRoot(true);

		try {

			processBatchOperation(state, 0);

			state.execute(resultList);

		} catch (SQLException e) {

			log.error("Delete failed", e);
			rollback();
			throw e;

		} catch (Throwable e) {

			log.error("Delete failed", e);
			rollback();
			throw e;

		} finally {

			state.release();
			allStatements = null;
			currentStatement = null;

		}

		log.debug("Delete successfully");

		return this;
	}

	/*
	 * This function relies on a ORM framework, which helps to load data though
	 * the proxy of a object automatically
	 * 
	 * public Transaction nestedBatchDelete(String sql, Object[] targets,
	 * NestedStatement[][] statements) throws InproperDivisionOfSqlException,
	 * SQLException, Throwable { // here the order does matter since the arrays
	 * in the first dimension is // parallel for (NestedStatement[] stats :
	 * statements) processNestedBatchDelete(stats, 0); // process the root
	 * operation finally processBatchDelete(sql, targets, extraMapper,
	 * aliasMapper);
	 * 
	 * extraMapper = null; aliasMapper = null;
	 * 
	 * return this; }
	 */

	/**
	 * This should only be called when used as stand-alone, since transaction
	 * may be managed by other frameworks if they are used
	 */
	public void commit() {

		try {

			conn.commit();

		} catch (SQLException e) {

			log.error("Commit failed", e);
			rollback();

		} finally {

			if (null != conn) {

				try{
				conn.close();
				}catch (SQLException e){}
				conn = null;

			}
			flush();
		}

	}
	

	public void rollback() {

		try {

			conn.rollback();

		} catch (SQLException e) {

			log.error("Rollback failed", e);
			

		} finally {

			if (null != conn) {

				try {
					conn.close();
				} catch (SQLException e) {}
				conn = null;

			}
			flush();

		}
	}

	public void flush() {

		registry = null;
		parser = null;
		resultList = null;
	}

	public Transaction setAliasedParameter(int index, String parameter)
	         throws Throwable {

		if(null == parameter)
			throw new NovelhibernateException("Alias can not be NULL");
		
		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setAliasedParameter(index, parameter);

		return this;

	}

	public Transaction setPredefinedParameter(int index, Object parameter)
			throws Throwable {

		if(null == parameter)
			throw new NovelhibernateException("Predefined parameter can not be NULL");
		
		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setPredefinedParameter(index, parameter);

		return this;

	}

	public Transaction setNestedStatement(String sql, Object[] targets)
			throws Throwable {

		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setSql(sql);
		currentStatement.setTargets(targets);
		currentStatement.setOperation(getOperation(sql));

		if (allStatements == null)
			allStatements = new ArrayList<NestedStatement>();

		allStatements.add(currentStatement);
		currentStatement = null;

		return this;

	}

	public Transaction setNestedStatement(String sql, String getAttrLowerState)
			throws Throwable {

		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setSql(sql);
		currentStatement.setOperation(getOperation(sql));
		currentStatement.setAcquiredName(getAttrLowerState.trim());

		if (allStatements == null)
			allStatements = new ArrayList<NestedStatement>();

		allStatements.add(currentStatement);
		currentStatement = null;

		return this;

	}

	public Transaction setHigherNestedStatement(String sql, Object[] targets,
			boolean isReversed) throws Throwable {

		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setSql(sql);
		currentStatement.setTargets(targets);

		currentStatement.setStatements(allStatements);
		currentStatement.setOperation(getOperation(sql));
		currentStatement.setReversed(isReversed);

		allStatements = new ArrayList<NestedStatement>();

		allStatements.add(currentStatement);
		currentStatement = null;

		return this;

	}

	public Transaction setHigherNestedStatement(String sql,
			String getAttrLowerState, boolean isReversed) throws Throwable {

		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setSql(sql);
		currentStatement.setAcquiredName(getAttrLowerState.trim());

		currentStatement.setStatements(allStatements);
		currentStatement.setOperation(getOperation(sql));
		currentStatement.setReversed(isReversed);

		allStatements = new ArrayList<NestedStatement>();

		allStatements.add(currentStatement);
		currentStatement = null;

		return this;

	}

	public Transaction setHigherTransitionalStatement() throws Throwable {

		if (currentStatement == null)
			currentStatement = new NestedStatement();

		currentStatement.setStatements(allStatements);

		allStatements = new ArrayList<NestedStatement>();

		allStatements.add(currentStatement);
		currentStatement = null;

		return this;

	}

	public LinkedList<Object> getResultList() {
		return resultList;
	}

	/**
	 * Create PreparedStatement and build ArgumentWrapper list, so they can be used later 
	 * @param statement the nested statement
	 * @throws Exception any exception
	 */
	@SuppressWarnings("unchecked")
	private void extractOperationArguement(NestedStatement statement)
			throws Exception {

		PreparedStatement prep = conn.prepareStatement(statement.getSql());

		Object list = registry.getRegisteredValue(statement.getSql()
				.replaceAll("\\s*", ""));

		List<ArgumentWrapper> wrappers = null;

		if (null != list) {

			wrappers = (List<ArgumentWrapper>) list;

			addToStatement(statement, 0);

		} else if (statement.getOperation() == Transaction.SAVE) {

			wrappers = new ArrayList<ArgumentWrapper>();

			// execute the first time to get the layout of fields
			executeInsertStatement(wrappers, prep, statement.getTargets()[0],
					false);

		} else {

	

			Object[] parameters = null;
			if (statement.getOperation() == Transaction.DELETE){
				
				// If the operation is delete, then a restriction is needed
				// when parsing the delete sql, because delete sql does not allow
				// to use alias, therefore it is need to identify which attributes 
				// needs to be extract from target object, which is not.
				List<Integer> restrict = new ArrayList<Integer>();
				if (null != statement.getExtraMapper())
					for (ParameterMapper mapper : statement.getExtraMapper())
						restrict.add(mapper.getIndex());

				if (null != statement.getAliasMapper())
					for (ParameterMapper mapper : statement.getAliasMapper())
						restrict.add(mapper.getIndex());
				parameters = parser
						.parseDeleteSQL(statement.getSql(), restrict);
				
			}else if (statement.getOperation() == Transaction.UPDATE)
				parameters = parser.parseUpdateSQL(statement.getSql(), (statement
						.getTargets()[0] == null)? null : statement
								.getTargets()[0].getClass());

			wrappers = new ArrayList<ArgumentWrapper>();

			// Set the static parameter to prepared statement
			if (null != statement.getExtraMapper())
				for (ParameterMapper mapper : statement.getExtraMapper())
					mapper.getPreparedStatementSetMethod().invoke(prep, mapper.getIndex() + 1,
							mapper.getParameter());

			// Get the layout of fields
			executeUpdateAndDeleteStatement(wrappers, prep, statement
					.getTargets()[0], parameters, statement.getAliasMapper(),
					false);

		}

		statement.setPrep(prep);
		statement.setWrapper(wrappers);

	}

	/**
	 * Operation for either nested or un-nested batch operation, note that for
	 * nested operation, the automatic function that retrieve data can not be
	 * used without the support of a ORM framework, since it just call the get
	 * method and trigger the proxy. Alternatively, one can preload the data
	 * into an object, but this is obviously case a duplicate loop and as the
	 * array will be decomposed again in the lower levels.
	 * 
	 * 
	 * The root should be the one side in typical one-to-many relationship
	 * 
	 * @param statement
	 *            the root or sub-root statement
	 * @param pop
	 *            the index of current sub-root statement, ignore for the
	 *            root statement 
	 * @throws InproperDivisionOfSQLException
	 *             throw if sql does not has alias for update statement
	 * @throws SQLException
	 *             other sql exceptions
	 * @throws Throwable
	 *             any throwable events
	 */
	@SuppressWarnings("unchecked")
	private void processBatchOperation(NestedStatement statement, int pop)
			throws InproperDivisionOfSQLException, SQLException, Throwable {

		Object[] targets = null;
		if (statement.isRoot()) {

			targets = statement.getTargets();

		} else {
			
			// the pop here is the key from previous loop for changing children
			// elements of a father
			// must be cautions when modify here
			// due to all the children element are shared the same source list
			// as they are all in the same level
			// therefore only the index 0 one has been used as an sample
			targets = (statement.getStatements().get(0).getAcquiredName() != null) ? retrieveTargets(
					statement, statement.getStatements().get(0), pop)
					: statement.getTargets();

		}

		/*
		 * iterator all the targets
		 */
		for (int j = 0; j < targets.length; j++) {

			if (null == statement.getPrep() && null != statement.getSql())
				extractOperationArguement(statement);

			if (null != statement.getStatements()) {
				for (NestedStatement state : statement.getStatements()) {

					if (null != state.getSql()) {

						// This exception is a restriction that ensures all the operations
						// is valid for being executed in the same level, otherwise considering split them up
						// or use alias parameter (x.y.z etc.)
						if (state.getTargets() != null
								&& targets.length != state.getTargets().length)
							throw new NovelhibernateException(
									"The target object number of father and child at the same level can not be different, considering split them by switching to different levels or just simply use the alias parameter");

						// The pop here is the key for changing children
						// elements of a father
						// must be cautions when modify here
						//
						// This will retrieve the targets for next loop
						if (null == state.getTargets())
							state.setTargets(retrieveTargets(statement, state,
									pop));

						if (null == state.getPrep())
							extractOperationArguement(state);
						// jump into the next loop
						if (state.getStatements() != null)
							processBatchOperation(state, j);

						addToStatement(state, j);
				

						if (j == targets.length - 1)
							state.setTargets(null);
					// Transitional statement
					} else if (state.getStatements() != null) {

						state.setTargets(targets);
						processBatchOperation(state, j);

					}

				}
			}

	
			if (statement.isRoot())
				addToStatement(statement, j);

		}

	}

	/**
	 * Get the actual data for next loop (if collection, then it is need the lazy load 
	 * function that provided by external ORM) 
	 * @param statement
	 * @param state
	 * @param pop
	 * @return
	 * @throws Exception
	 */
	private Object[] retrieveTargets(NestedStatement statement,
			NestedStatement state, int pop) throws Exception {

		Object object = statement.getTargets()[pop].getClass().getMethod(
				"get" + convertToMethodName(state.getAcquiredName())).invoke(
				statement.getTargets()[pop]);

		/*
		 * for (Object o : ((Collection)object).toArray())
		 * System.out.print(o+":"+":\n");
		 */
		// If the returning one is collection, then convert it to array
		// otherwise return a object array that contain the returned object
		return (object instanceof Collection) ? ((Collection<?>) object)
				.toArray() : new Object[] { object };
	}

	/**
	 * Call setXXX method in prepared statement 
	 * @param statement the statement
	 * @param pop the index of current target
	 * @throws Exception
	 */
	private void addToStatement(NestedStatement statement, int pop)
			throws Exception {

		if (statement.getOperation() == Transaction.SAVE) {

			Object var = null;
			for (int i = 0; i < statement.getWrapper().size(); i++) {
				var = callNestedMethod(statement.getTargets()[pop], statement
						.getWrapper().get(i).getMethodArray(), 0);

				// This is need due to primitive type (int, short)
				// can not be null
				if (var == null)
					var = nullSafe(statement.getWrapper().get(i)
							.getTargetClass());

				statement.getWrapper().get(i).getPreparedStatementSetMethod()
						.invoke(statement.getPrep(), i + 1, var);
			}
		} else {

			if (null != statement.getExtraMapper())
				for (ParameterMapper mapper : statement.getExtraMapper())
					mapper.getPreparedStatementSetMethod().invoke(statement.getPrep(),
							mapper.getIndex() + 1, mapper.getParameter());

			for (int i = 0; i < statement.getWrapper().size(); i++)
				statement.getWrapper().get(i).getPreparedStatementSetMethod()
						.invoke(
								statement.getPrep(),
								statement.getWrapper().get(i).getIndex() + 1,
								callNestedMethod(statement.getTargets()[pop],
										statement.getWrapper().get(i)
												.getMethodArray(), 0));

		}
		statement.getPrep().addBatch();

	}

	private PreparedStatement executeInsertStatement(
			List<ArgumentWrapper> wrappers, PreparedStatement prep,
			Object target, boolean isExecute) throws Exception {

		Field[] fields = target.getClass().getDeclaredFields();

		Field field = null;

		// the custom foreign entity
		Object customObject = null;

		ArgumentWrapper wrapper = null;

		Method[] methods = null;

		int index = 0;
		for (int i = 0; i < fields.length; i++) {

			field = fields[i];
			if (!field.isAnnotationPresent(PojoNonMapDeclaration.class)) {
				
				wrapper = new ArgumentWrapper();

				if (field
						.isAnnotationPresent(PojoForeignEntityDeclaration.class)) {

					methods = new Method[2];

					methods[0] = target.getClass().getMethod(
							"get" + nameToMethod(field.getName()));

					String key = field.getAnnotation(
							PojoForeignEntityDeclaration.class)
							.externalEntityColumnName();

					methods[1] = field.getType().getMethod(
							"get" + nameToMethod(key));

					wrapper.setTargetClass(filterToPrimitive(field.getType()
							.getDeclaredField(key).getType()));

					wrapper.setMethodArray(methods);
					// get the actual custom variable
					customObject = methods[0].invoke(target);
					// get the variable that needs to put into DBMS
					if (null != customObject)
						wrapper.setVariable(methods[1].invoke(customObject));

				} else {

					methods = new Method[1];
					methods[0] = target.getClass().getMethod(
							"get" + nameToMethod(field.getName()));

					wrapper.setMethodArray(methods);
					// get the actual variable type
					wrapper.setTargetClass(filterToPrimitive(field.getType()));
					// get the type of alternative variable
					wrapper.setVariable(methods[0].invoke(target));

				}

				// get the actual set method within prepared statement
				wrapper.setPreparedStatementSetMethod(prep.getClass()
						.getMethod(
								"set"
										+ convertToMethodName(wrapper
												.getTargetClass().getName()),
								new Class[] { Integer.TYPE,
										wrapper.getTargetClass() }));

				if (isExecute) {
					if (wrapper.getVariable() == null)
						wrapper.setVariable(nullSafe(wrapper.getTargetClass()));

					wrapper.getPreparedStatementSetMethod().invoke(prep,
							index + 1, wrapper.getVariable());
				}
				wrapper.clean();
				// add to cache list
				if (null != wrappers)
					wrappers.add(wrapper);

				index++;
			}
		}

		return prep;
	}

	private PreparedStatement executeUpdateAndDeleteStatement(
			List<ArgumentWrapper> wrappers, PreparedStatement prep,
			Object target, Object[] vars,
			List<ParameterMapper> localAliasMapper, boolean isExecute)
			throws Exception {

		ArgumentWrapper wrapper = null;

		ParameterMapper mapper = null;

		for (int j = 0; j < vars.length; j++) {

			mapper = (ParameterMapper) vars[j];

			wrapper = new ArgumentWrapper();

			wrapUpdateAndDeleteParameter(wrapper, mapper, target, prep,
					isExecute);
			// add to cache list
			if (null != wrappers)
				wrappers.add(wrapper);
		}

		if (null != localAliasMapper)
			for (int i = 0; i < localAliasMapper.size(); i++) {

				mapper = localAliasMapper.get(i);

				wrapper = new ArgumentWrapper();

				wrapUpdateAndDeleteParameter(wrapper, mapper, target, prep,
						isExecute);

				// add to cache list
				if (null != wrappers)
					wrappers.add(wrapper);
			}

		return prep;
	}

	private void wrapUpdateAndDeleteParameter(ArgumentWrapper wrapper,
			ParameterMapper mapper, Object target, PreparedStatement prep,
			boolean isExecute) throws Exception {

		wrapper.setIndex(mapper.getIndex());
		
		
        // The getParameter would return a String of alias, rather than the
		// actual value
		String[] aliases = ((String) mapper.getParameter()).split("\\.");

		Method[] methods = new Method[aliases.length];

		wrapper.setVariable(nestedMethod(target, methods, aliases, 0));

		wrapper.setMethodArray(methods);

		wrapper.setTargetClass(filterToPrimitive(wrapper.getVariable()
				.getClass()));

		wrapper
				.setPreparedStatementSetMethod(prep.getClass().getMethod(
						"set"
								+ convertToMethodName(wrapper.getTargetClass()
										.getName()),
						new Class[] { Integer.TYPE, wrapper.getTargetClass() }));

		if (isExecute)
			wrapper.getPreparedStatementSetMethod().invoke(prep,
					mapper.getIndex() + 1, wrapper.getVariable());

		wrapper.clean();
	}

	private Object callNestedMethod(Object target, Method[] methods, int pop)
			throws Exception {

		if (pop == methods.length - 1)
			return methods[pop].invoke(target);
		else
			return (methods[pop].invoke(target) == null) ? null
					: callNestedMethod(methods[pop].invoke(target), methods,
							pop + 1);
	}

	/**
	 * Parse the nested method by target and given method chain (in string)
	 * This is normally called at the first time, later on if there is need
	 * to invoke the nested method, then use callNestedMethod
	 * @param target
	 * @param methods
	 * @param chain
	 * @param pop
	 * @return
	 * @throws Exception
	 */
	private Object nestedMethod(Object target, Method[] methods,
			String[] chain, int pop) throws Exception {

		methods[pop] = target.getClass().getMethod(
				"get" + nameToMethod(chain[pop]));
		if (pop == chain.length - 1)
			return methods[pop].invoke(target);

		else
			return (methods[pop].invoke(target) == null) ? null : nestedMethod(
					methods[pop].invoke(target), methods, chain, pop + 1);
	}

	/**
	 * Parse and identify the type of sql, then return the indicator
	 * @param sql
	 * @return
	 */
	private int getOperation(String sql) {

		if (sql.trim().toLowerCase().startsWith("update"))
			return Transaction.UPDATE;
		else if (sql.trim().toLowerCase().startsWith("delete"))
			return Transaction.DELETE;
		else
			return Transaction.SAVE;

	}

}
